﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PalcoCentral.Database
{
    public class PersonDBAdapter
    {
        private static PersonDBAdapter instance = null;

        public static PersonDBAdapter getInstance()
        {
            if (instance == null)
            {
                instance = new PersonDBAdapter();
            }
            
            return instance;
        }

        //Porquê singleton? Para estarmos sempre dentro do mesmo contexto facilitando assim as coisas e diminuindo acessos desnecessários.
        //Em Web é melhor não fazer isto por causa de multiplos acessos, pode dar barraca
        private Database.PalcoCentraBDEntities entity;
        private PersonDBAdapter()
        {
            this.entity = ContextAdapterSingleton.getInstance().entity;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="login"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public Library.OperationStatus Authenticate(string login, string password)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                var res = from a in entity.admin
                          where a.login == login
                          select a.person;
                person p = res.FirstOrDefault<person>();
                if (p.admin.password.CompareTo(password)!=0)
                {
                    throw new Exception("Password Inválida");
                }
                if (p != null)
                {
                    os.res=p;
                    os.sucess = true;
                }

            } catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro no Login.";
            }
            return os;
        }

        #region Person CRUD

        private person addPerson(Library.Person per)
        {
            person p = new person();
          
                p.address = per.Address;
                p.archiveIdBI = per.AchieveIdBi;
                p.bi = per.Bi;
                p.dateOfBirth = per.DateOfBirth;
                p.email = per.Email;
                p.emergencyContact = per.EmergencyContact;
                p.emissionBI = per.BiEmission;
                p.locality = per.Locality;
                p.mobile_number = per.Mobile;
                p.nif = per.Nif;
                p.observationsPerson = per.Observations;
                p.personName = per.Name;
                p.personRole = per.PersonRole.ToString();
                p.personStatus = "Activo";
                p.postalCode = per.PostalCode;
                p.registrationDate = DateTime.Now;
                p.telephone = per.Telephone;
                if (per.PersonId != null && per.PersonId > 0)
                {
                    p.personId = per.PersonId;
                }

                //return entity.person.Add(p); ;

     
                return p;

            
            //return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="student"></param>
        /// <returns>sucess = true|false && res with student</returns>
        public Library.OperationStatus AddStudent(Library.Student student)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
               
                    student.PersonRole = Library.PersonRole.Student;
                    person p = addPerson(student);
                    if (p == null)
                    {
                        throw new Exception("Can't insert person!");
                    }

                    student sToDB = new student();

                    sToDB.person = p;
                    sToDB.personId = p.personId;

                    // sToDB.person = p;
                    sToDB.personId = p.personId;
                    sToDB.educationResp = student.EducationResp;
                    sToDB.educationRespEmail = student.EducationRespEmail;
                    sToDB.educationRespMobile = student.EducationRespMobile;
                    //sToDB.monthlyPayment = student.MontanteMensalidade;

                    // sToDB.
                    student s = entity.student.Add(sToDB);
                    Library.Utils.saveState(entity);
                    os.res = s;
                    os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a inserir estudante na base de dados. Verifique os campos.";
            }
            return os;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="per"></param>
        /// <returns>sucess = true|false && res with teacher</returns>
        public Library.OperationStatus AddTeacher(Library.Person per)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
               
                    per.PersonRole = Library.PersonRole.Teacher;
                    person p = addPerson(per);
                    p = entity.person.Add(p);
                    Library.Utils.saveState(entity);
                    if (p == null)
                    {
                        throw new Exception("Can't insert person!");
                    }


                    os.res = p;
                    os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a inserir professor na base de dados. Verifique os campos.";
            }
            return os;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="per"></param>
        /// <param name="login"></param>
        /// <param name="password"></param>
        /// <returns>sucess = true|false && res with admin</returns>
        public Library.OperationStatus AddAdmin(Library.Person per, string login, string password)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
              
                    per.PersonRole = Library.PersonRole.Admin;
                    person p = addPerson(per);
                    if (p == null)
                    {
                        throw new Exception("Can't insert person!");
                    }

                    admin adm = new admin();
                    adm.login = login;
                    adm.password = password;
                    adm.person = p;
                    adm.personId = p.personId;

                    adm = entity.admin.Add(adm);
                    Library.Utils.saveState(entity);
                    os.res = adm;
                    os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a administrador estudante na base de dados. Verifique os campos.";
            }
            return os;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns>sucess = true|false</returns>
        //public  Library.OperationStatus ChangePersonStatus(int id)
        //{
        //    Library.OperationStatus os = new Library.OperationStatus();
        //    try
        //    {
                

        //            var p = entity.person.Where(w => w.personId == id).SingleOrDefault();

        //            if (p == null)
        //            {
        //                throw new Exception("Can't find person!");
        //            }
        //            else
        //            {
        //                p.personStatus = "Inactivo";
        //                entity.SaveChanges();
        //            }


        //            os.res = p;
        //            os.sucess = true;
                
        //    }
        //    catch (Exception ex)
        //    {
        //        os.sucess = false;
        //        os.ex = ex;
        //        os.errorMessage = "Erro a inserir professor na base de dados. Verifique os campos.";
        //    }
        //    return os;
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="per"></param>
        /// <returns>sucess = true|false && res with new person</returns>
        public  Library.OperationStatus editPerson(person per)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
               
                    //person p = entity.person.Where(w => w.personId == per.personId).SingleOrDefault();
                    //p = per;
                    Library.Utils.saveState(entity);
                    //TODO
                    os.res = per;
                    os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a editar professor na base de dados. Verifique os campos.";
            }
            return os;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="per"></param>
        /// <returns>sucess = true|false && res with new person</returns>
        public Library.OperationStatus editAdmin(admin per)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                Library.Utils.saveState(entity);
                //TODO
                os.res = per;
                os.sucess = true;

            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a editar admin na base de dados. Verifique os campos.";
            }
            return os;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="student"></param>
        /// <returns>sucess = true|false && res with new Student</returns>
        public Library.OperationStatus editStudent(person student)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                Library.Utils.saveState(entity);
                    os.res = student;
                    os.sucess = true;
           
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Ocorreu um erro a inserir as alterações na base de dados. Possivel motivo: " + ex.Message;
            }
            return os;
        }

        /// <summary>
        /// needs person ID
        /// </summary>
        /// <param name="mp"></param>
        /// <returns>sucess = true|false</returns>
        public Library.OperationStatus removePerson(person person)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {

                if (person.personRole.CompareTo("Student") == 0)
                {
                    List<monthlypayment> x = entity.monthlypayment.Where(w => w.studentId == person.personId).ToList(); ;
                    foreach (var item in x)
                    {
                        entity.monthlypayment.Remove(item);
                    }
                }
                entity.person.Remove(person);
                Library.Utils.saveState(entity);
                os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a remover professor na base de dados. Verifique os campos.";
            }
            return os;
        }




        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="query"></param>
        /// <returns>sucess = true|false && res with list of Persons </returns>
        public Library.OperationStatus searchPersons(string query)
        {
            //query = RemoveDiacritics(query).ToLower();
            query = query.ToLower();
            long biQuery = -1000;
            long.TryParse(query, out biQuery);
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
               
                    // var SqlQueryRes = entity.person.Where(p => (p.personRole != "Admin" && RemoveDiacritics(p.personName).ToLower().Contains(query) && query.Contains(RemoveDiacritics(p.personName).ToLower()) && biQuery == p.bi));

                    var SqlQueryRes = from p in entity.person
                                      where (p.personRole != "Admin" && (p.personName.ToLower().Contains(query) || query.Contains(p.personName.ToLower())))
                                      select p;
                    List<person> persons = SqlQueryRes.ToList<person>();


                    os.res = persons;
                    os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a fazer pesquisa. Verifique se não tem caracteres especiais.";
            }
            return os;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="query"></param>
        /// <returns>sucess = true|false && res with list of Persons </returns>
        public Library.OperationStatus listOfAdmins()
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                var SqlQueryRes = from p in entity.person
                                  where (p.personRole == "Admin")
                                  select p;
                List<person> persons = SqlQueryRes.ToList<person>();


                os.res = persons;
                os.sucess = true;

            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a retornar lista de Admins.";
            }
            return os;

        }


        /// <summary>
        /// Convert a person to Student (Get the additional info)
        /// </summary>
        /// <param name="p"></param>
        /// <returns>sucess = true|false && res with Student</returns>
        public Library.OperationStatus personToStudent(person p)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
               
                    // var SqlQueryRes = entity.person.Where(p => (p.personRole != "Admin" && RemoveDiacritics(p.personName).ToLower().Contains(query) && query.Contains(RemoveDiacritics(p.personName).ToLower()) && biQuery == p.bi));

                    var SqlQueryRes = from s in entity.student
                                      where s.personId == p.personId
                                      select s;
                    student stu = SqlQueryRes.SingleOrDefault();
                    stu.person = p;

                    os.res = stu;
                    os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a fazer pesquisa. Verifique se não tem caracteres especiais.";
            }
            return os;
        }

        private string RemoveDiacritics(string sIn)
        {
            string sFormD = sIn.Normalize(NormalizationForm.FormD);
            StringBuilder sb = new StringBuilder();

            foreach (char ch in sFormD)
            {
                UnicodeCategory uc = CharUnicodeInfo.GetUnicodeCategory(ch);
                if (uc != UnicodeCategory.NonSpacingMark)
                {
                    sb.Append(ch);
                }
            }

            return (sb.ToString().Normalize(NormalizationForm.FormC));
        }


        #region Payment
        /// <summary>
        /// 
        /// </summary>
        /// <param name="mp"></param>
        /// <returns>sucess = true|false && res with new Monthly Payment</returns>
        public Library.OperationStatus addPayment(monthlypayment mp)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                
                    mp = entity.monthlypayment.Add(mp);
                    Library.Utils.saveState(entity);
                    os.res = mp;
                    os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a inserir professor na base de dados. Verifique os campos.";
            }
            return os;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="mp"></param>
        /// <returns>sucess = true|false</returns>
        public Library.OperationStatus pay(monthlypayment mp )
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                
                    //mp.isPay = 1; //TODO MUDA BD
                    monthlypayment p = entity.monthlypayment.Where(w => w.monthlypaymentId == mp.monthlypaymentId).SingleOrDefault();
                    p = mp;
                    Library.Utils.saveState(entity);
                    
                    os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a inserir professor na base de dados. Verifique os campos.";
            }
            return os;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="mp"></param>
        /// <returns>sucess = true|false</returns>
        public Library.OperationStatus deletePayment(monthlypayment mp )
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
               

                    entity.monthlypayment.Remove(mp);
                    Library.Utils.saveState(entity);
                    os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a inserir professor na base de dados. Verifique os campos.";
            }
            return os;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="mp"></param>
        /// <returns>sucess = true|false && res with list of Monthly Payments</returns>
        public Library.OperationStatus showPayments(int studentId)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
               
                   // List<monthlypayment> p = entity.monthlypayment.Where(w => w.studentId == studentId ).OrderBy(w=> w.year descending).ToList();
                List<monthlypayment> p = (from mp in entity.monthlypayment
                                          where mp.studentId == studentId
                                          orderby mp.monthlypaymentId descending

                                          select mp).ToList();
                    os.res = p;
                    os.sucess = true;
                
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a inserir professor na base de dados. Verifique os campos.";
            }
            return os;
        }


        #endregion
    }
}
